# -*- coding:utf-8 -*-
import datetime
import requests
import pymysql
import time
import re
from lxml import etree
from selenium import webdriver
import sys

db1 = pymysql.connect(host='112.125.25.114', port=3306, user="python", password="pythonselect", database="mykhadmin")
cursor1 = db1.cursor()

db2 = pymysql.connect(host="121.4.69.146", user='root', password='xhhg@2020', port=3306, db="big_data",
                      use_unicode=True, charset='utf8')
cursor2 = db2.cursor()

ipc_list = ['A01C', 'A01G', 'A01B', 'A01D', 'A01K', 'A01M', 'A21D', 'A23B', 'A23C', 'A23F', 'A23G', 'A23K', 'A23L',
            'A23N', 'A41D', 'A43B', 'A47C', 'A47B', 'A47G', 'A47J', 'A47K', 'A47L', 'A61G', 'A61H', 'A61J', 'A61K36/+',
            'A62B', 'A63B', 'C05G', 'C08L', 'C09D', 'C10M', 'C11D', 'C12G']
num = 1


def is_ok(browser):
    page_source = browser.page_source
    html = etree.HTML(page_source)
    chrc = ''.join(html.xpath('//span[@id="selectyzm_text_dz"]/text()'))
    if '成功' in chrc:
        return True
    else:
        return False


def is_all_in(chrc, aim_char):
    for char in chrc:
        if char not in aim_char:
            return False
        else:
            continue
    return True


def is_login_ok(browser):
    page_source = browser.page_source
    html = etree.HTML(page_source)
    chrc = ''.join(html.xpath('//p/text()'))
    if '关于多国发明专利审查信息查询使用声明' in chrc:
        print('声明页面登录成功')
    else:
        print('声明页面没登录进去')
        time.sleep(3)
        is_login_ok(browser)


def is_search_ok(browser):
    page_source = browser.page_source
    html = etree.HTML(page_source)
    chrc = ''.join(html.xpath('//p/text()'))
    if '费用信息' in chrc:
        print('查询页面登录成功')
    else:
        print('查询页面没登录进去')
        time.sleep(10)
        is_search_ok(browser)


def login_cpquery(login_inst):
    """登录中国及多国"""
    profile = webdriver.FirefoxOptions()
    profile.set_preference('network.proxy.type', 1)
    browser = webdriver.Firefox(options=profile, executable_path=r'D:\谷歌下载\chromedriver_win32(0)\geckodriver.exe')
    browser.get("http://cpquery.cnipa.gov.cn/")
    time.sleep(15)

    if login_inst == 'XH':
        browser.find_element_by_id('username').clear()
        browser.find_element_by_id('username').send_keys('CN00675769')
        time.sleep(1)
        browser.find_element_by_id('password').clear()
        browser.find_element_by_id('password').send_keys('Gv89NWWY@')
    elif login_inst == 'XHHG':
        browser.find_element_by_id('username').clear()
        browser.find_element_by_id('username').send_keys('42264')
        time.sleep(1)
        browser.find_element_by_id('password').clear()
        browser.find_element_by_id('password').send_keys('Xhhg0613@@')
    elif login_inst == 'WB':
        # 15978554102 Hhr31415926!
        # 17362997328 Ccy1994!
        # 15090891351 Ccy1994!
        # 13296529358 king123!B
        browser.find_element_by_id('username1').clear()
        browser.find_element_by_id('username1').send_keys('13283766953')
        time.sleep(1)
        browser.find_element_by_id('password1').clear()
        browser.find_element_by_id('password1').send_keys('Ws1234567890~')
    else:
        print('请手动输入密码')
        time.sleep(35)
    time.sleep(5)
    is_search_ok(browser)
    current_url = browser.current_url
    return browser, current_url


def get_login():
    headers = {"Accept": "application/json"}
    url = 'https://api.xinhehonggu.com/api/auth/login'
    phone = '18866668888'
    password = '888888'
    source = 'admin'
    data = {
        'method': '1',
        'phone': phone,
        'password': password,
        'source': source,
    }
    r = requests.post(url=url, data=data, headers=headers, verify=False)
    res_dict = r.json()
    # print(res_dict['message'])
    token = res_dict['data']['token']
    return token


def get_content():
    sql = "select application_number,order_number,name,status,organization from finals where application_number !=''"
    cursor1.execute(sql)
    content = cursor1.fetchall()
    return content


def get_content_xhhg():
    sql = "select application_number,case_status from xhhg_pantent_status where case_status ='';"
    cursor2.execute(sql)
    content = cursor2.fetchall()
    return content


def into_sql(db_name, cursor_name, item):
    sql = 'insert into xhhg_pantent_status (application_number,sq_date,case_status,fm_name,fm_class,created_at,updated_at,agency,patent_user,first_agent,port,fmr) values ("{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}");'.format(
        item['sqh'], item['sq_date'], item['case_status'], item['fm_name'], item['fm_class'], item['created_at'],
        item['updated_at'], item['agency'], item['patent_user'], item['first_agent'], item['port'], item['fmr'])
    try:
        cursor_name.execute(sql)
        db_name.commit()
    except Exception as e:
        print(e)
        print(sql)
        print('---------error---------', item)


def update_xhhg_sql(db_name, cursor_name, item):
    sql = "update xhhg_pantent_status set case_status= '{}',agency='{}',fm_name='{}',fm_class='{}',sq_date='{}',updated_at='{}' ,patent_user='{}' ,first_agent = '{}',port = '{}',fmr = '{}'where application_number = '{}';".format(
        item['case_status'], item['agency'], item['fm_name'], item['fm_class'], item['sq_date'], item['updated_at'],
        item['patent_user'], item['first_agent'], item['port'], item['fmr'], item['sqh'])
    try:
        cursor_name.execute(sql)
        db_name.commit()
    except Exception as e:
        print(e)
        print('---------error---------', item)
        db_name.rollback()


def get_sqh_ipclass():
    sql = "select application_number,fm_class,case_status from xhhg_pantent_status where case_status ='等待实审提案'"
    cursor2.execute(sql)
    content = cursor2.fetchall()
    return content


def get_dayli_download_file(sqh):
    sql1 = "select patent_name, `type` from priorityReview where Application='{}'".format(sqh.replace('ZL', ''))
    cursor2.execute(sql1)
    content = cursor2.fetchall()
    if content:
        is_issue_file = True
    else:
        is_issue_file = False
    return is_issue_file


def judge_examination(sqh):
    sql1 = "select consumer_pay_status,consumer_pay_date from finals where application_number='ZL{}'".format(sqh)
    cursor1.execute(sql1)
    content = cursor1.fetchall()
    is_issue_file = False  # 是否下发了审查意见
    if content:
        for i in content:
            content_str = i[0]
            if content_str:
                file_name = content_str.split('/')[-1]
                print('file_name:', file_name)
                if '审查意见' in content_str:
                    is_issue_file = True
    if not is_issue_file:
        is_issue_file = get_dayli_download_file(sqh)
    print(sqh, is_issue_file)
    return is_issue_file


def update_priority_reviewl():
    # 拿到案件状态为 等待实审提案 的相关信息
    content1 = get_sqh_ipclass()
    print(f"等待实审提案的申请号有{len(content1)}个")
    num = 0
    for i in range(len(content1)):
        # 2.更新本地 xhhg_pantent_status：将案件分类不在 ipc_list 的案件的 priority_reviewl 字段设置为1
        if content1[i][1].split(' ')[0] not in ipc_list:
            sqh = content1[i][0].strip()
            is_issue_file = judge_examination(sqh)
            if not is_issue_file:
                sql = "update xhhg_pantent_status set priority_reviewl= '1' where application_number like '%{}%';".format(
                    sqh)
                num += 1
                print(num)
                try:
                    cursor2.execute(sql)
                    db2.commit()
                except Exception as e:
                    print(e)
                    print('---------error---------')
                    db2.rollback()


def get_info(db, cursor):  # 获取0813增加的更新申请优先审查状态、发起申请优先审查角色
    sql = "select * from xhhg_pantent_status"
    cursor.execute(sql)
    content = cursor.fetchall()
    return content


def update_retry(num, url, headers, item):
    if num < 10:
        try:
            num += 1
            response = requests.post(url, headers=headers, data=item).json()
            print(response, 'lwh_table')
        except:
            print('重试第{}次'.format(num))
            time.sleep(2)
            update_retry(num, url, headers, item)


def update_lwh_table(item, token):
    headers = {"Accept": "application/json",
               "authorization": "Bearer " + token}
    url = f"https://api.xinhehonggu.com/api/finals/update/patent/status"

    update_retry(num, url, headers, item)


def update_system_status():
    # 1.拿到案件状态为 等待实审提案 的相关信息
    # 2.更新本地 xhhg_pantent_status：将案件分类不在 ipc_list 的案件的 priority_reviewl 字段设置为1
    # 3.拿到本地 xhhg_pantent_status 表所有的数据
    # 4.遍历取值上传 https://api.xinhehonggu.com/api/finals/update/patent/status 接口

    token = get_login()  # 获取token
    update_priority_reviewl()  # 更新优先审查信息 不能注释掉
    content1 = get_info(db2, cursor2)
    priority_reviewl_num = 0
    for i in range(len(content1)):
        item = {}
        item['application_number'] = content1[i][1]
        item['case_status'] = content1[i][2]
        item['fm_name'] = content1[i][3]
        item['fm_class'] = content1[i][4]
        item['sq_date'] = content1[i][5]
        item['priority_reviewl'] = content1[i][8]
        item['agency'] = content1[i][9]
        if not item['priority_reviewl']:
            item['priority_reviewl'] = 0
        else:
            priority_reviewl_num += 1
            print('priority_reviewl_num:', priority_reviewl_num)
        print('当前正在更新第{}条数据，共{}条！'.format(i + 1, len(content1)))
        print(item)
        if not item['agency']:
            del item['agency']
        if not item['fm_class']:
            del item['fm_class']
        if item['case_status']:
            update_lwh_table(item, token)
            print('更新完成')
            pass
        print('-' * 150)
    print('可以做优先审查的案件: ', priority_reviewl_num)


def parse_Description_item_change(html, sqh, port):
    item_li = html.xpath('//*[@id="bgid"]/table/tbody/tr')
    # print('item_li_len:',len(item_li))
    if len(item_li) > 1:
        for i in item_li[1:]:
            item = {}
            item['sqh'] = sqh
            item['port'] = port
            item['change_date'] = ''.join(i.xpath('.//td[1]/span/@title'))
            item['changes'] = ''.join(i.xpath('.//td[2]/span/@title'))
            item['before_change'] = ''.join(i.xpath('.//td[3]/span/@title'))
            item['after_change'] = ''.join(i.xpath('.//td[4]/span/@title'))
            item['create_date'] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            print(item)
            sql = 'insert into description_item_change(sqh, change_date, changes, before_change, after_change, create_date, port) values ("{}", "{}", "{}", "{}","{}", "{}", "{}");'.format(
                item['sqh'], item['change_date'], item['changes'], item['before_change'], item['after_change'],
                item['create_date'], item['port'])
            try:
                cursor2.execute(sql)
                db2.commit()
            except Exception as e:
                print(e)
                print(sql)


def get_sqh():
    with open('sqh.txt', 'r') as f:
        content = f.read()
        f.close()
    sqh_list = content.split('\n')
    sqh_content = []
    for sqh in sqh_list:
        sqh_li = []
        sqh = sqh.strip().replace('ZL', '')
        sqh_li.append(sqh)
        sqh_content.append(sqh_li)
    return sqh_content


def parse_status(browser, current_url, sqh, login_inst, i):
    pat = ''.join(re.findall(r'select-key:shenqingh=(.*?)&select-key', current_url))
    url = re.sub(pat, sqh, current_url)
    browser.get(url)
    # time.sleep(1)
    if '著录项目信息' in browser.page_source:
        html = etree.HTML(browser.page_source)
        try:
            status = ''.join(html.xpath('//td[text()="案件状态："]/following-sibling::td[1]/span/@title'))
            sq_date = ''.join(html.xpath('//td[text()="申请日："]/following-sibling::td[1]/span/@title'))
            fm_name = ''.join(html.xpath('//td[text()="发明名称："]/following-sibling::td[1]/text()')).strip()
            fm_class = ''.join(html.xpath('//td[text()="主分类号 ："]/following-sibling::td[1]/span/@title'))
            agency = ''.join(html.xpath('//td[text()="代理机构名称："]/following-sibling::td[1]/span/@title'))
            patent_user = ','.join(html.xpath('//*[@id="sqrid"]/table/tbody/tr/td[1]/span/@title')).strip()
            first_agent = ''.join(html.xpath('//*[@id="zldlid"]/div[2]/table/tbody/tr/td[2]/span/@title'))
            fmr = ''.join(html.xpath('//*[@id="fmrid"]/table/tbody/tr/td[2]/span/@title'))
        except Exception as e:
            print(e)
            status = ''
            sq_date = ''
            fm_name = ''
            fm_class = ''
            agency = ''
            patent_user = ''
            first_agent = ''
            fmr = ''
        item = {}
        item['sqh'] = 'ZL' + sqh
        item['sq_date'] = sq_date
        item['case_status'] = status
        item['fm_name'] = fm_name
        item['fm_class'] = fm_class
        item['agency'] = agency
        item['first_agent'] = first_agent
        item['created_at'] = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        item['updated_at'] = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        item['patent_user'] = patent_user
        item['fmr'] = fmr
        item['port'] = login_inst
        print("item: ", item)
        if login_inst == 'XH':
            if item['case_status'] == '':
                item['port'] = ''
            into_sql(db2, cursor2, item)
        else:
            if item['case_status'] != '':
                update_xhhg_sql(db2, cursor2, item)
        try:
            parse_Description_item_change(html, sqh, login_inst)  # 入库
        except Exception as e:
            print('error:', e)
    elif '账号登录' in browser.page_source:
        print('\n', f"\033[1;31m 账号被挤或异常掉线，已退出！当前断点数字：{i}\033[0m", '\n')
        sys.exit()
    else:
        time.sleep(2)
        parse_status(browser, current_url, sqh, login_inst, i)


def get_len():
    sql = "SELECT COUNT(application_number) AS num FROM xhhg_pantent_status"
    cursor2.execute(sql)
    n = cursor2.fetchall()
    nu = n[0][0]
    return nu


def get_null_len():
    sql = "SELECT COUNT(case_status) sss FROM `xhhg_pantent_status` WHERE case_status = ''"
    cursor2.execute(sql)
    n = cursor2.fetchall()
    nu = n[0][0]
    return nu


def StartParse(login_inst):
    browser, current_url = login_cpquery(login_inst)
    start_time = datetime.datetime.now()
    if login_inst == 'XH':
        content = get_content()  # 获取需要爬取的专利号
        n = get_len()
    else:
        n = get_null_len()
        content = get_content_xhhg()  # 获取数据库中专利号
        # content = get_sqh()
    num = 0

    for i in range(int(n), len(content)):  # 2020207090033

        sqh = content[i][0].strip('ZL')
        print('当前正在获取第{}个专利申请号是{}的案件状态,共{}个！'.format(i + 1, sqh, len(content)))
        try:
            parse_status(browser, current_url, sqh, login_inst, i)
        except Exception as e:
            print(e)
            parse_status(browser, current_url, sqh, login_inst, i)
        estimated_time = ((datetime.datetime.now() - start_time) / (num + 1)) * (len(content) - i)
        num += 1
        hours_str = str(estimated_time).split(':')[0]
        minute_str = str(estimated_time).split(':')[1]
        second_str = str(estimated_time).split(':')[2].split('.')[0]
        if hours_str != '0':
            print('预计剩余时间：{}小时{}分钟{}秒'.format(hours_str, minute_str, second_str))
        else:
            print('预计剩余时间：{}分钟{}秒'.format(minute_str, second_str))
        print('*' * 100)


if __name__ == '__main__':
    # 1. 协和端口账号查询状态 第一遍查询插入  账号: CN00675769  密码: Gv89NWWY@  查询: 2020207090033
    # StartParse('XH')
    # 2. 信合红谷端口账号查询状态 第二遍查询更新  账号: 42264  密码: Xhhg0613@@  查询: 2019214697762
    # StartParse('XHHG')
    # 3 第三遍使用公众登录查询 查询: 2020207090033
    # StartParse('WB')
    # 4. 本地 xhhg_patnet_status	导入线上库，已经更新了优先审查信息
    update_system_status()
    pass
